package org.zhxy.core.personnel.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.zhxy.entity.personnel.Teacher;
import org.zhxy.entity.personnel.UserTeacher;

import java.util.List;
import java.util.Map;

@Mapper
public interface TeacherMapper extends BaseMapper<Teacher> {
    @Select("<script> " +
            " SELECT u.*,t.*, " +
                " ( SELECT GROUP_CONCAT(d.`label`) FROM sc_teacher_department td, sc_department d WHERE t.`id` = td.`teacher_id` AND td.`department_id` = d.`id` AND td.`status` = '1' ) AS departmentNames," +
                " ( SELECT GROUP_CONCAT(td.`department_id`) FROM sc_teacher_department td WHERE t.`id` = td.`teacher_id` AND td.`status` = '1' ) AS departmentIds, " +
                " ( SELECT GROUP_CONCAT(r.`name`) FROM pe_role r, pe_user_role ur WHERE t.`id` = u.`id` AND u.`id` = ur.`user_id` AND ur.`role_id` = r.`id` ) AS roleNames," +
                " ( SELECT GROUP_CONCAT(ur.`role_id`) FROM pe_user_role ur WHERE t.`id` = u.`id` AND u.`id` = ur.`user_id` ) AS roleIds, " +
                " ( SELECT tg.`general_status` FROM sc_teacher_general tg WHERE t.id = tg.user_id ) AS generalStatus, " +
                " ( SELECT tr.`resignation_status` FROM sc_teacher_resignation tr WHERE t.`id` = tr.`user_id` ) AS resignationStatus " +
            " FROM pe_user u, sc_teacher t " +
            " WHERE u.`id` = t.`id` " +
            " ORDER BY t.`entry_time` " +
            " LIMIT #{map.page},#{map.size} " +
            " </script>")
    List<UserTeacher> searchAllUserTeacherList(@Param("map") Map<String, Object> map);

    @Select("<script>" +
            " SELECT COUNT(id) AS total FROM sc_teacher  " +
            " </script>")
    long searchTotalCount(@Param("map") Map<String, Object> map);


    @Select("<script> SELECT DISTINCT t.*,u.*, " +
            " ( SELECT GROUP_CONCAT(d.`label`) FROM sc_teacher_department td, sc_department d WHERE t.`id` = td.`teacher_id` AND td.`department_id` = d.`id` AND td.`status` = '1' ) AS departmentNames, " +
            " ( SELECT GROUP_CONCAT(td.`department_id`) FROM sc_teacher_department td WHERE t.`id` = td.`teacher_id` AND td.`status` = '1' ) AS departmentIds, " +
            " ( SELECT GROUP_CONCAT(r.`name`) FROM pe_role r, pe_user_role ur WHERE t.`id` = u.`id` AND u.`id` = ur.`user_id` AND ur.`role_id` = r.`id` ) AS roleNames, " +
            " ( SELECT GROUP_CONCAT(ur.`role_id`) FROM pe_user_role ur WHERE t.`id` = u.`id` AND u.`id` = ur.`user_id` ) AS roleIds, " +
            " ( SELECT tg.`general_status` FROM sc_teacher_general tg WHERE t.id = tg.user_id ) AS generalStatus, " +
            " ( SELECT tr.`resignation_status` FROM sc_teacher_resignation tr WHERE t.`id` = tr.`user_id` ) AS resignationStatus " +
            " FROM sc_teacher t, sc_teacher_department td,pe_user u " +
            " WHERE t.`id` = td.`teacher_id` " +
            " AND t.`id` = u.id  " +
            " AND td.`status` = '1' " +
            " AND td.`department_id` IN ( " +
            " SELECT sd.id FROM sc_department sd WHERE sd.sub_school_id IN ( " +
            "  SELECT DISTINCT d.`sub_school_id` FROM sc_department d  WHERE d.sub_school_id IN (           " +
            "   SELECT DISTINCT d.sub_school_id  " +
            "   FROM sc_teacher t, sc_teacher_department td ,sc_department d " +
            "   WHERE t.`id` = td.`teacher_id` " +
            "   AND td.`department_id` = d.`id` " +
            "   AND td.`status` = '1' " +
            "   AND t.`id` = #{map.id} " +
            "  ) " +
            " ) " +
            ") " +
            "ORDER BY t.`entry_time` " +
            "LIMIT #{map.page},#{map.size}  </script>")
    List<UserTeacher> searchUserTeacherList(@Param("map")Map<String, Object> map);



    @Select("<script> SELECT COUNT(0) FROM ( " +
            " SELECT DISTINCT t.`id` " +
            " FROM sc_teacher t, sc_teacher_department td, pe_user u " +
            " WHERE t.`id` = td.`teacher_id` AND t.`id` = u.id AND td.`status` = '1' AND td.`department_id` IN ( " +
            "  SELECT sd.id FROM sc_department sd WHERE sd.sub_school_id IN ( " +
            "   SELECT DISTINCT d.`sub_school_id` FROM sc_department d  WHERE d.sub_school_id IN (           " +
            "     SELECT DISTINCT d.sub_school_id " +
            "     FROM sc_teacher t, sc_teacher_department td ,sc_department d " +
            "     WHERE t.`id` = td.`teacher_id` " +
            "     AND td.`department_id` = d.`id` " +
            "     AND td.`status` = '1' " +
            "     AND t.`id` = #{map.id} " +
            "   ) " +
            "  ) " +
            " ) " +
            ")  AS temp </script>")
    long searchCountByTeacher(@Param("map")Map<String, Object> map);
}
